<?php
/*首页数据接口*/
namespace Home\Controller;
use Think\Controller;
class UploadController extends Controller {
    public function studentExcel(){   
        import("Org.Util.PHPExcel");                                 // 导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
        $PHPExcel = new \PHPExcel();                                 // 创建PHPExcel对象，注意，不能少了\
        $exts="xlsx";
        if ($exts == 'xls') {                                        // 如果excel文件后缀名为.xls，导入这个类
            import("Org.Util.PHPExcel.Reader.Excel5");
            $PHPReader = new \PHPExcel_Reader_Excel5();
        } else if ($exts == 'xlsx') {
            import("Org.Util.PHPExcel.Reader.Excel2007");
            $PHPReader = new \PHPExcel_Reader_Excel2007();
        }
        $PHPExcel = $PHPReader->load("./Public/jdc_car_details.xlsx");                     // 载入文件
        $currentSheet = $PHPExcel->getSheet(0);                      // 获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
        
        $allRow = $currentSheet->getHighestRow();                    // 获取总行数
        $cellNameArr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S');
        $allColumn = count($cellNameArr);              // 获取总列数
        $columnArr=[
            'A'=>"carnum",
            'B'=>"base_info",
            'C'=>"config_spotlight",
            'D'=>"models_spotlight",
            'E'=>"body_config",
            'F'=>"engine_config",
            'G'=>"gearbox_config",
            'H'=>"chassis_steering",
            'I'=>"wheel_brake",
            'J'=>"safety_equip",
            'K'=>"control_config",
            'L'=>"outside_config",
            'M'=>"inside_config",
            'N'=>"chair_config",
            'O'=>"multimedia_config",
            'P'=>"light_config",
            'Q'=>"mirrors_config",
            'R'=>"aircondition_config",
            'S'=>"hightech_config"];
        
        for ($currentRow = 2; $currentRow <= $allRow; $currentRow ++) {// 循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
            for ($currentColumn = 0; $currentColumn < $allColumn; $currentColumn ++) {// 从哪列开始，A表示第一列
                $address = $cellNameArr[$currentColumn] . $currentRow;             // 数据坐标
                $carnum=$currentSheet->getCell("A".$currentRow)->getValue();
                $value=$currentSheet->getCell($address)->getValue();
                $content_arr=[];
                if($currentColumn>0){
                    $val_arr=explode('|',$value);
                    foreach($val_arr as $v){
                        $val_arr1=explode(':',$v);
                        $content_arr[]=$val_arr1;
                    }
                    $ExlData[$carnum][$columnArr[$cellNameArr[$currentColumn]]] = json_encode($content_arr);// 读取到的数据，保存到数组$arr中
                }
                    
            }
        }
        $car_details = M('car_details');  
        // 生成数据库对象
        foreach($ExlData as $key=>$val){
            $result=$car_details->where("carnum='".$key."'")->save($val);
            print($car_details->getLastSql());  
            var_dump($result);
        }
    }

    public function insertExcel(){
        import("Org.Util.PHPExcel");
        $PHPExcel = new \PHPExcel();
        $exts="xls";
        if ($exts == 'xls') {                                        // 如果excel文件后缀名为.xls，导入这个类
            import("Org.Util.PHPExcel.Reader.Excel5");
            $PHPReader = new \PHPExcel_Reader_Excel5();
        } else if ($exts == 'xlsx') {
            import("Org.Util.PHPExcel.Reader.Excel2007");
            $PHPReader = new \PHPExcel_Reader_Excel2007();
        }
        $PHPExcel = $PHPReader->load("./Public/jdc_user.xls");
        $currentSheet = $PHPExcel->getSheet(0);  
        $allRow = $currentSheet->getHighestRow();
        // print_r($allRow);die;
        $cellNameArr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ');
        $allColumn = count($cellNameArr);
        $columnArr=[
            'A'=>"fullanme",
            'B'=>"nickname",
            'C'=>"tel",
            'D'=>"minority",
            'E'=>"identify",
            'F'=>"address",
            'G'=>"birthday",
            'H'=>"sex",
            'I'=>"bank",
            'J'=>"banker",
            'K'=>"banknum",
            'L'=>"marriage",
            'M'=>"workunit",
            'N'=>"income",
            'O'=>"creditcard",
            'P'=>"borrowing",
            'Q'=>"identify_url",
            'R'=>"driver_url",
            'S'=>"workproof_url",
            'T'=>"bankflow_url",
            'U'=>"creditreport_url",
            'V'=>"create_time",
            'W'=>"housingnature",
            'X'=>"policeoffice",
            'Y'=>"liveaddress",
            'Z'=>"companyaddress",
            'AA'=>"otherdata_url",
            'AB'=>"credittype",
            'AC'=>"contacts_phone",
            'AD'=>"contacts_name",
            'AE'=>"contacts_bearing",
            'AF'=>"contacts_bearing",
            'AG'=>"contacts_phone_tel",
            'AH'=>"contacts_user_name",
            'AI'=>"contacts_sip_bearing",
            'AJ'=>"type_status",
            'AK'=>'orderid',
            'AL'=>'provinceid',
            'AM'=>'cityid',
            'AN'=>'car_num',
            'AO'=>'contract',
            'AP'=>'stockid',
            'AQ'=>'colors',
            'AR'=>'carid',
            'AS'=>'create_time',
            'AT'=>'enginenum',
            'AU'=>'framenum',
            'AV'=>'contract_time',
            'AW'=>'buy_type',
            'AX'=>'gps',
            'AY'=>'purchase_tax',
            'AZ'=>'insurance',
            'BA'=>'uu_price',
            'BB'=>'first_pay',
            'BC'=>'month_pay',
            'BD'=>'pay_month',
            'BE'=>'ordertype_status',
            'BF'=>'deposit_state',
            'BG'=>'depositnum',
            'BH'=>'contract_url',
            'BI'=>'facesign_url',
            'BJ'=>'payment_stage',
            'BK'=>'first_payment_stage',
            'BL'=>'receivables_time',
            'BM'=>'finance_time',
            'BN'=>'finance_audit',
            'BO'=>'risk_time',
            'BP'=>'riskmember',
            'BQ'=>'riskmember_time',
            'BR'=>'riskmember_jl',
            'BS'=>'sale_time',
            'BT'=>'saleuser',
            'BU'=>'salecontrac_time',
            'BV'=>'salecontracuser',
            'BW'=>'onthecards_time',
            'BX'=>'onthecards_user',
            'BY'=>'user_time',
            'BZ'=>'user',
        ];
         $ExlDataArr = [];
         for ($currentRow = 2; $currentRow <= $allRow; $currentRow ++) {// 循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始 
            $content_arr=[];
            for ($currentColumn = 0; $currentColumn < $allColumn; $currentColumn ++) {// 从哪列开始，A表示第一列
                $address = $cellNameArr[$currentColumn] . $currentRow;             // 数据坐标
                $carnum=$currentSheet->getCell("A".$currentRow)->getValue();
                $value=$currentSheet->getCell($address)->getValue();
                if(is_object($value))  $value= $value->__toString();
                $content_arr[]=$value;
                $ExlData[$columnArr[$cellNameArr[$currentColumn]]] = $value;
            }
            if($ExlData['fullanme'] && $ExlData['nickname'] && $ExlData['tel']){
                $ExlDataArr[] = $ExlData;
            }
        }
        
        foreach ($ExlDataArr as $key => $value) {
            $userarr = [
                'fullanme'=>$value["fullanme"],
                'nickname'=>$value["nickname"],
                'tel'=>$value["tel"],
                'minority'=>$value["minority"],
                'identify'=>$value["identify"],
                'address'=>$value["address"],
                'birthday'=>$value["birthday"],
                'sex'=>$value["sex"],
                'bank'=>$value["bank"],
                'banker'=>$value["banker"],
                'banknum'=>$value["banknum"],
                'marriage'=>$value["marriage"],
                'workunit'=>$value["workunit"],
                'income'=>$value["income"],
                'creditcard'=>$value["creditcard"],
                'borrowing'=>$value["borrowing"],
                'create_time'=> strtotime($value["create_time"]),
                'housingnature'=>$value["housingnature"],
                'policeoffice'=>$value["policeoffice"],
                'liveaddress'=>$value["liveaddress"],
                'companyaddress'=>$value["companyaddress"],
                'credittype'=>$value["credittype"],
                'contacts_phone'=>$value["contacts_phone"],
                'contacts_name'=>$value["contacts_name"],
                'contacts_bearing'=>$value["contacts_bearing"],
                'contacts_bearing'=>$value["contacts_bearing"],
                'contacts_phone_tel'=>$value["contacts_phone_tel"],
                'contacts_user_name'=>$value["contacts_user_name"],
                'contacts_sip_bearing'=>$value["contacts_sip_bearing"],
            ];
            $userid = M('user')->add($userarr);
            $path = "./Public/Image/Ceshi/".$userid;
            if(!file_exists($path)){
                mkdir($path, 0777,true);
                mkdir($path.'/identify_url/', 0777,true);
                mkdir($path.'/driver_url/', 0777,true);
                mkdir($path.'/workproof_url/', 0777,true);
                mkdir($path.'/bankflow_url/', 0777,true);
                mkdir($path.'/creditreport_url/', 0777,true);
                mkdir($path.'/otherdata_url/', 0777,true);
            }
            $identify_arr=explode(',',$value["identify_url"]);
            foreach ($identify_arr as $k => $v) {
                $identify_arr[$k] = $path.'/identify_url/'.$v;
            }
            $driver_arr=explode(',',$value["driver_url"]);
            foreach ($driver_arr as $k => $v) {
                $driver_arr[$k] = $path.'/driver_url/'.$v;
            }
            $workproof_arr=explode(',',$value["workproof_url"]);
            foreach ($workproof_arr as $k => $v) {
                $workproof_arr[$k] = $path.'/workproof_url/'.$v;
            }
            $bankflow_arr=explode(',',$value["bankflow_url"]);
            foreach ($bankflow_arr as $k => $v) {
                $bankflow_arr[$k] = $path.'/bankflow_url/'.$v;
            }
            $creditreport_arr=explode(',',$value["creditreport_url"]);
            foreach ($creditreport_arr as $k => $v) {
                $creditreport_arr[$k] = $path.'/creditreport_url/'.$v;
            }
            $otherdata_arr=explode(',',$value["otherdata_url"]);
            foreach ($otherdata_arr as $k => $v) {
                $otherdata_arr[$k] = $path.'/otherdata_url/'.$v;
            }
            $userimgurl = [
                'identify_url'=>serializeMysql($identify_arr),
                'driver_url'=>serializeMysql($driver_arr),
                'workproof_url'=>serializeMysql($workproof_arr),
                'bankflow_url'=>serializeMysql($bankflow_arr),
                'creditreport_url'=>serializeMysql($creditreport_arr),
                'otherdata_url'=>serializeMysql($otherdata_arr),
            ];
           
            M('user')->where(['id'=>$userid])->save($userimgurl);
            $qcgl   = M('qcgl_manage',null);
            $where['qcmc'] = array('like','%'.$value['carid'].'%');
            $qcgldata = $qcgl->where($where)->find();
            $path =  './Public/Uploads/payment/'.$userid;
            if(!is_dir($path)){
                  mkdir($path,0777,true);
                  mkdir($path.'/contract_url/', 0777,true);
                  mkdir($path.'/facesign_url/', 0777,true);
            }
            $facesign_arr=explode(',',$value["facesign_url"]);
            foreach ($facesign_arr as $k => $v) {
                $facesign_arr[$k] = $path.'/facesign_url/'.$v;
            }
            $orderarr = [
                'orderid' => $value['orderid'],
                'uid' => $userid,
                'provinceid' => $value['provinceid']?1:1,
                'cityid'  => $value['cityid']?2:2,
                'car_num' => $value['car_num'],
                'contract'=> $value['contract'],
                'stockid' => $value['stockid']?0:0,
                'colors'  => $value['colors'],
                'car_num' => $value['car_num'],
                'carid'   => $qcgldata['id'],
                'enginenum'   => $value['enginenum'],
                'framenum'   => $value['framenum'],
                'create_time'   => strtotime($value['create_time']),
                'contract_time'   => strtotime($value['contract_time']),
                'buy_type'   => $value['buy_type'],
                'gps'   => $value['gps'],
                'purchase_tax'   => $value['purchase_tax'],
                'insurance'   => $value['insurance'],
                'uu_price'   => $value['uu_price'],
                'first_pay'   => $value['first_pay'],
                'month_pay'   => $value['month_pay'],
                'deposit_state'   => $value['deposit_state'],
                'depositnum'   => $value['depositnum'],
                'contract_url'   => serializeMysql($value['contract_url']),
                'facesign_url'   => serializeMysql($facesign_arr),
                'status'   =>6,
            ];
            $orderoperation = M('order_operation');
            $orderid = M('order')->add($orderarr);
            $orderarr = [
                'orderid'=>$value['orderid'],
                'usernum'=>$value['saleuser'],
                'status' =>0,
                'remark' =>"创建订单",
                'create_time' =>strtotime($value['sale_time']),
            ];  
            $orderoperation->add($orderarr);
            $financeauditarr = [
                'orderid'=>$value['orderid'],
                'usernum'=>$value['finance_audit'],
                'status' =>101,
                'remark' =>"财务核算",
                'create_time' =>strtotime($value['riskmember_time']),
            ]; 
            $orderoperation->add($financeauditarr);
            $riskmemberarr = [
                'orderid'=>$value['orderid'],
                'usernum'=>$value['riskmember'],
                'status' =>1,
                'remark' =>"风控审核",
                'create_time' =>strtotime($value['risk_time']),
            ]; 
            $orderoperation->add($riskmemberarr);

            $riskmemberjlarr = [
                'orderid'=>$value['orderid'],
                'usernum'=>$value['riskmember_jl'],
                'status' =>1,
                'remark' =>"风控经理审核",
                'create_time' =>strtotime($value['risk_time']),
            ]; 
            $orderoperation->add($riskmemberjlarr);

            $alearr = [
                'orderid'=>$value['orderid'],
                'usernum'=>$value['salecontracuser'],
                'status' =>3,
                'remark' =>"销售签约",
                'create_time' =>strtotime($value['salecontrac_time']),
            ];
            $orderoperation->add($alearr);
            $financearr = [
                'orderid'=>$value['orderid'],
                'usernum'=>$value['finance_audit'],
                'status' =>4,
                'remark' =>"财务确认收款",
                'create_time' =>strtotime($value['receivables_time']),
            ];
            $orderoperation->add($financearr);
            $onthecardsarr = [
                'orderid'=>$value['orderid'],
                'usernum'=>$value['onthecards_user'],
                'status' =>5,
                'remark' =>"车辆上牌",
                'create_time' =>strtotime($value['onthecards_time']),
            ];
            $orderoperation->add($onthecardsarr);
            $userarr = [
                'orderid'=>$value['orderid'],
                'usernum'=>$value['user'],
                'status' =>6,
                'remark' =>"用户提车",
                'create_time' =>strtotime($value['user_time']),
            ];
            $orderoperation->add($userarr);

        }
        
        print_r($userarr);die;
       
    }
}
